﻿using MgSoft.Excel.Model;
using MgSoft.Excel.Model.Excel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Text;
using System.Linq;
using MgSoft.Excel.Model.Template;
using System.Threading.Tasks;

namespace MgSoft.Excel.Util
{
    public static class MgSheetUtil
    {
        public static MgSheet ConvertToMgSheet(ISheet sheet, TemplateConfig templateConfig, List<ExcelErrorMessage> errorMessages)
        {
            var keyCellConfig = templateConfig.GetKeyCellConfig();

            MgSheet mgSheet = new MgSheet();

            //根据不同的匹配方案修改模板

            buildeTemplateConfig(sheet, mgSheet, templateConfig, errorMessages);

            //转换关键数据
            ConvertDataKey(sheet, mgSheet, keyCellConfig, errorMessages);
            ConvertDataExtX(sheet, mgSheet, templateConfig, errorMessages);
            ConvertDataExtY(sheet, mgSheet, templateConfig, errorMessages);
            ConvertDataExtC(sheet, mgSheet, templateConfig, errorMessages);

            return mgSheet;

        }



        private static void ConvertDataExtC(ISheet sheet, MgSheet mgSheet, TemplateConfig templateConfig, List<ExcelErrorMessage> errorMessages)
        {
            var dataTemplateConfig = templateConfig.TemplateCellConfigs.Where(p => p.IsKey == false && p.Ext == Ext.C).ToList();
            foreach (var item in dataTemplateConfig)
            {
                var cell = sheet.GetRow(item.StartRow).GetCell(item.ColumnIndex);
                mgSheet.Add(new MgCell()
                {
                    CellDataType = item.Type,
                    ColumnIndex = item.ColumnIndex,
                    ColumnName = item.Name,
                    RowIndex = item.StartRow,
                    Value = GetCellValue(cell, item)
                });
            }
        }

        private static void ConvertDataExtY(ISheet sheet, MgSheet mgSheet, TemplateConfig templateConfig, List<ExcelErrorMessage> errorMessages)
        {
            //todo
        }

        private static void ConvertDataExtX(ISheet sheet, MgSheet mgSheet, TemplateConfig templateConfig, List<ExcelErrorMessage> errorMessages)
        {
            var dataTemplateConfig = templateConfig.TemplateCellConfigs.Where(p => p.IsKey == false && p.Ext == Ext.X).ToList();
            int startRowIndex = mgSheet.StartRowIndex;
            int maxRowIndex = mgSheet.MaxRowIndex;

            foreach (var item in dataTemplateConfig)
            {
                var columnIndex = item.ColumnIndex;

                for (int rowIndex = startRowIndex; rowIndex <= maxRowIndex; rowIndex++)
                {
                    //System.Diagnostics.Debug.WriteLine(item.Name + ";" + rowIndex);
                    //数据校验错误信息
                    List<ExcelErrorMessage> convertErrorMessages = new List<ExcelErrorMessage>();
                    var cell = sheet.GetRow(rowIndex).GetCell(columnIndex);

                    CheckCellValueByCellConfig(cell, item, rowIndex, columnIndex, convertErrorMessages);//校验数据

                    //校验成功则把cell转换为MgCell
                    if (convertErrorMessages.Count == 0)
                    {
                        mgSheet.Add(new MgCell()
                        {
                            CellDataType = item.Type,
                            ColumnIndex = columnIndex,
                            ColumnName = item.Name,
                            RowIndex = rowIndex,
                            Value = GetCellValue(cell, item)
                        });
                    }
                    else
                    {
                        errorMessages.AddRange(convertErrorMessages);
                    }
                }
            };
        }

        /// <summary>
        /// 转换关键数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="mgSheet"></param>
        /// <param name="keyCellConfig"></param>
        /// <param name="errorMessages"></param>
        private static void ConvertDataKey(ISheet sheet, MgSheet mgSheet, TemplateCellConfig keyCellConfig, List<ExcelErrorMessage> errorMessages)
        {

            int rowIndex = keyCellConfig.StartRow;
            int columnIndex = keyCellConfig.ColumnIndex;

            if (rowIndex > sheet.LastRowNum) return;//表格没有数据
            if (columnIndex > sheet.GetRow(rowIndex).LastCellNum) return;

            ((IMgSheet)mgSheet).SetStartRowIndex(rowIndex);

            int sheetRowIndex = rowIndex;
            while (sheetRowIndex <= sheet.LastRowNum)
            {
                //数据校验错误信息
                List<ExcelErrorMessage> convertErrorMessages = new List<ExcelErrorMessage>();
                var cell = sheet.GetRow(sheetRowIndex)?.GetCell(columnIndex);

                //如果读不到数据则结束循环
                if (cell == null || string.IsNullOrEmpty(cell.ToString()))
                {
                    break;
                }

                CheckCellValueByCellConfig(cell, keyCellConfig, rowIndex, columnIndex, convertErrorMessages);//校验数据

                //校验成功则把cell转换为MgCell
                if (convertErrorMessages.Count == 0)
                {
                    mgSheet.Add(new MgCell()
                    {
                        CellDataType = keyCellConfig.Type,
                        ColumnIndex = columnIndex,
                        ColumnName = keyCellConfig.Name,
                        RowIndex = sheetRowIndex,
                        Value = GetCellValue(cell, keyCellConfig)
                    });
                }
                else
                {
                    errorMessages.AddRange(convertErrorMessages);
                }

                ((IMgSheet)mgSheet).SetMaxRowIndex(sheetRowIndex);
                sheetRowIndex++;
            }
        }

        private static object GetCellValue(ICell cell, TemplateCellConfig cellConfig)
        {
            object returnValue;

            if (cell == null) return null;

            switch (cellConfig.Type)
            {
                case CellDataType.String:
                    returnValue = string.IsNullOrWhiteSpace(cell.ToString()) ? "" : cell.ToString();
                    break;
                case CellDataType.Int:
                    returnValue = Convert.ToInt32(cell.NumericCellValue);
                    break;
                case CellDataType.Double:
                    returnValue = Convert.ToDouble(cell.NumericCellValue);
                    break;
                case CellDataType.Decimal:
                    returnValue = Convert.ToDecimal(cell.NumericCellValue);
                    break;
                case CellDataType.Date:
                    returnValue = cell.DateCellValue;
                    break;
                case CellDataType.NullableInt:
                    returnValue = getInt32Value(cell);
                    break;
                case CellDataType.NullableDouble:
                    returnValue = getDoubleValue(cell);
                    break;
                case CellDataType.NullableDecimal:
                    returnValue = getDecimalValue(cell);
                    break;
                case CellDataType.NullableDate:
                    returnValue = getDateValue(cell);
                    break;
                default: throw new MgExcelException($"[{cellConfig.Type}]未定义的类型");
            }
            return returnValue;
        }

        private static DateTime? getDateValue(ICell cell)
        {
            short format = cell.CellStyle.DataFormat;
            //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
            if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20 || format == 22 || format == 176 || format == 165)
            {
                return cell.DateCellValue;
            }
            else if (cell.CellType == CellType.String)
            {
                if (DateTime.TryParse(cell.StringCellValue, out var result))
                    return result;
            }
            return null;
        }

        private static double? getDoubleValue(ICell cell)
        {
            if (cell.CellType == CellType.Numeric)
            {
                if (double.TryParse(cell.NumericCellValue.ToString(), out var result))
                    return result;
            }
            else
            {
                if (double.TryParse(cell.StringCellValue.ToString(), out var result))
                    return result;
            }
            return null;
        }
        private static decimal? getDecimalValue(ICell cell)
        {
            if (cell.CellType == CellType.Numeric)
            {
                if (decimal.TryParse(cell.NumericCellValue.ToString(), out var result))
                    return result;
            }
            else
            {
                if (decimal.TryParse(cell.StringCellValue.ToString(), out var result))
                    return result;
            }
            return null;
        }

        private static Int32? getInt32Value(ICell cell)
        {
            if (cell.CellType == CellType.Numeric)
            {
                if (Int32.TryParse(cell.NumericCellValue.ToString(), out var result))
                    return result;
            }
            else
            {
                if (Int32.TryParse(cell.StringCellValue.ToString(), out var result))
                    return result;
            }
            return null;
        }


        private static object GetCellValue(ICell cell)
        {
            object returnValue = "";
            if (cell == null)
            {
                returnValue = "";
            }
            else
            {
                //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)***
                switch (cell.CellType)
                {
                    case CellType.Blank:
                        returnValue = "";
                        break;
                    case CellType.Numeric:
                        short format = cell.CellStyle.DataFormat;
                        //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                        if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20)
                            returnValue = cell.DateCellValue;
                        if (format != 0 && cell.DateCellValue.GetType() == typeof(DateTime))
                        {
                            returnValue = cell.DateCellValue;
                        }
                        else
                        {
                            returnValue = cell.NumericCellValue;
                        }
                        break;
                    case CellType.String:
                        returnValue = cell.StringCellValue;
                        break;
                }
            }
            return returnValue;
        }
        //private static string convertToString(ICell cell, TemplateCellConfig cellConfig)
        //{
        //    if (cell.CellType == CellType.String) return cell?.ToString();
        //    if (cell.CellType == CellType.Boolean) return cell?.BooleanCellValue.ToString();
        //}

        private static void CheckCellValueByCellConfig(ICell cell, TemplateCellConfig keyCellConfig, int rowIndex, int columnIndex, List<ExcelErrorMessage> errorMessages)
        {
            CheckIsRequired(cell, keyCellConfig, rowIndex, columnIndex, errorMessages);
            CheckType(cell, keyCellConfig, rowIndex, columnIndex, errorMessages);
            //CehckIsRequire(cell, keyCellConfig);
        }

        //private static void CehckIsRequire(ICell cell, TemplateCellConfig keyCellConfig)
        //{
        //    throw new NotImplementedException();
        //}

        private static void CheckType(ICell cell, TemplateCellConfig keyCellConfig, int rowIndex, int columnIndex, List<ExcelErrorMessage> errorMessages)
        {
            //数据为空则不检查类型
            if (cell == null)
            {
                return;
            }

            try
            {
                GetCellValue(cell, keyCellConfig);
            }
            catch (Exception e)
            {
                string message = $"第{columnIndex}列的内容类型应为[{CellDataTypeUtil.GetDataTypeCaption(keyCellConfig.Type)}]";
                errorMessages.Add(new ExcelErrorMessage(rowIndex, columnIndex, message));
            }
        }

        private static void CheckIsRequired(ICell cell, TemplateCellConfig keyCellConfig, int rowIndex, int columnIndex, List<ExcelErrorMessage> errorMessages)
        {
            //不允许为空
            if (keyCellConfig.IsRequired)
            {
                if (string.IsNullOrEmpty(cell?.ToString()))
                {
                    var columnCaption = keyCellConfig.Caption;
                    errorMessages.Add(new ExcelErrorMessage(rowIndex, columnIndex, $@"【{columnCaption}】列没有值"));
                }
            }
        }

        private static void buildeTemplateConfig(ISheet sheet, MgSheet mgSheet, TemplateConfig templateConfig, List<ExcelErrorMessage> errorMessages)
        {
            var templateMatchType = templateConfig.TemplateConfigMatchingType;

            switch (templateMatchType)
            {
                case TemplateMatchingType.MatchByCaption:
                    buildAtMatchByCaption(sheet, mgSheet, templateConfig, errorMessages);
                    break;
                case TemplateMatchingType.MatchByColumnName:
                    break;
                case TemplateMatchingType.MatchByColumNameAndCaption:
                    buildAtMatchByColumNameAndCaption(sheet, mgSheet, templateConfig, errorMessages);
                    break;
                case TemplateMatchingType.Ignore:
                    break;
            }
        }

        #region TemplateMatchingTypeMethod

        /// <summary>
        /// 根据标题名(Caption)匹配，修改模板的列名(ColumName)
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="mgSheet"></param>
        /// <param name="templateConfig"></param>
        private static void buildAtMatchByCaption(ISheet sheet, MgSheet mgSheet, TemplateConfig templateConfig, List<ExcelErrorMessage> errorMessages)
        {
            var firstRow = sheet.GetRow(0);

            foreach (var config in templateConfig.TemplateCellConfigs)
            {
                for (int i = 0; i < firstRow.Count(); i++)
                {
                    var cellValue = firstRow.GetCell(i).StringCellValue;
                    if (config.Caption == cellValue)
                    {
                        var columName = SheetColumnNameUtil.ColumnIndexToColumnName(i);
                        config.ColumnName = columName;
                    }
                }
            }
        }

        /// <summary>
        /// 这个方法只做校验
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="mgSheet"></param>
        /// <param name="templateConfig"></param>
        /// <param name="errorMessages"></param>
        private static void buildAtMatchByColumNameAndCaption(ISheet sheet, MgSheet mgSheet, TemplateConfig templateConfig, List<ExcelErrorMessage> errorMessages)
        {
            var firstRow = sheet.GetRow(0);

            foreach (var config in templateConfig.TemplateCellConfigs)
            {
                for (int i = 0; i < firstRow.Count(); i++)
                {
                    var cellValue = firstRow.GetCell(i).StringCellValue;
                    if (config.Caption == cellValue)
                    {
                        var ExcelColumName = SheetColumnNameUtil.ColumnIndexToColumnName(i);
                        var TemplateConfigColumName = config.ColumnName;
                        if (ExcelColumName != TemplateConfigColumName)
                        {
                            errorMessages.Add(new ExcelErrorMessage($@"Excel表格第一行标题【{cellValue}】所在列与模板设置不匹配！！！"));
                        }
                    }
                }
            }
        }
        #endregion
    }
}
